<?php
namespace app\crm\model;
use think\Model;
use think\Db;
use think\Cache;
use think\Loader;

class Reportmanager extends Model
{   
    /**
     * 发货列表
     * @return [type] [description]
     */
    public function performanceReportData()
	{
		$page = $_REQUEST["page"];
		$limit = $_REQUEST["limit"];
		$createtime_start = $_REQUEST['createtime_start'];
		$createtime_end = $_REQUEST['createtime_end'];
		$create_user = $_REQUEST['create_user'];
		$dept_id = $_REQUEST['dept_id'];
		$username = $_SESSION["user_info"]["username"];
		$d_id = $_SESSION['user_info']['d_id'];
		$arrDep = $this->getDepTreeData();
		$deptst = $this->getMeAndSubDeptID($arrDep,$dept_id);  //取上级部门
		$deptSet = rtrim($deptst,",");
		
		$deptst2 = $this->getMeAndSubDeptID($arrDep,$d_id);  //取上级部门
		$deptSet2 = rtrim($deptst2,",");
		
		if(!$dept_id){
			$deptId = $deptSet2;
		}else{
			$deptId = $deptSet;
		}
		$where = "1 ";
		$where .= empty($createtime_start) ? "" : " AND createtime >= '$createtime_start'";
		$where .= empty($createtime_end) ? "" : " AND createtime <= '$createtime_end'";
		$where .= empty($create_user) ? "" : " AND createname = '$create_user'";
		if($username != "admin" || $dept_id){
			$where .= " AND dept_id in ($deptId)";
		}
		
		$fields = "createname,dept_id,count(*) as total_order_num,
			sum(cope_money) as total_cope_money,
			sum(case when order_status = '1' then 1 else 0 end) as actual_sales_num,
			sum(if(order_status = '1',`cope_money`,0)) as actual_sales_money,
			sum(case when order_status = '3' then 1 else 0 end) as pending_audit_num,
			sum(if(order_status = '3',`cope_money`,0)) as pending_audit_money,
			sum(case when order_status = '8' then 1 else 0 end) as problem_num,
			sum(if(order_status = '8',`cope_money`,0)) as problem_money,
			sum(case when order_status = '7' then 1 else 0 end) as not_pass_num,
			sum(if(order_status = '7',`cope_money`,0)) as not_pass_money,
			sum(case when order_status = '2' then 1 else 0 end) as cancel_num,
			sum(if(order_status = '2',`cope_money`,0)) as cancel_money
		";
		
		//print_r($fields);
		$arrCount = Db::name('order_info')->Distinct(true)->field("createname")->where($where)->select();
		$count = count($arrCount);
		$sql = "SELECT ".$fields." FROM `xy_order_info` WHERE ".$where."  GROUP BY `createname` ORDER BY createname asc LIMIT ".($page-1)*$limit.",".$limit;
		//$dataList = Db::name('order_info')->fetchSql(true)->field($fields)->where($where)->limit(($page-1)*$limit,$limit)->order("createname asc")->group("createname")->select();
		$dataList = Db::query($sql);
		$department = Cache::get('department');
		$users = Cache::get('users');
		
		foreach($dataList as $key=>&$val){
			$val["dept_name"] = $department[$val["dept_id"]]["department_name"];
			$val["cn_name"] = $users[$val["createname"]]["cn_name"];
			
			//实际销售单数/总订单数
			$val["audit_rate2"] = ($val["actual_sales_num"]/$val["total_order_num"])*100;
			$val["audit_rate"] = round($val["audit_rate2"],2)."%";;  //审核率
			
		}
		
		$rowsList = count($dataList) ? $dataList : false;
        $arrCate["code"] = 0;
        $arrCate["msg"] = '';
        $arrCate["count"] = $count;
        $arrCate["data"] = $rowsList;
        return $arrCate;
		
	}


    public function deptPerformanceReportData()
	{
		$page = $_REQUEST["page"];
		$limit = $_REQUEST["limit"];
		$createtime_start = $_REQUEST['createtime_start'];
		$createtime_end = $_REQUEST['createtime_end'];
		$dept_id = $_REQUEST['dept_id'];
		$username = $_SESSION["user_info"]["username"];
		$d_id = $_SESSION['user_info']['d_id'];
		$arrDep = $this->getDepTreeData();
		$deptst = $this->getMeAndSubDeptID($arrDep,$dept_id);  //取上级部门
		$deptSet = rtrim($deptst,",");
		
		$deptst2 = $this->getMeAndSubDeptID($arrDep,$d_id);  //取上级部门
		$deptSet2 = rtrim($deptst2,",");
		
		if(!$dept_id){
			$deptId = $deptSet2;
		}else{
			$deptId = $deptSet;
		}
		$where = "1 ";
		$where .= empty($createtime_start) ? "" : " AND createtime >= '$createtime_start'";
		$where .= empty($createtime_end) ? "" : " AND createtime <= '$createtime_end'";
		if($username != "admin" || $dept_id){
			$where .= " AND dept_id in ($deptId)";
		}
		
		$fields = "dept_id,count(*) as total_order_num,
			sum(cope_money) as total_cope_money,
			sum(case when order_status = '1' then 1 else 0 end) as actual_sales_num,
			sum(if(order_status = '1',`cope_money`,0)) as actual_sales_money,
			sum(case when order_status = '3' then 1 else 0 end) as pending_audit_num,
			sum(if(order_status = '3',`cope_money`,0)) as pending_audit_money,
			sum(case when order_status = '8' then 1 else 0 end) as problem_num,
			sum(if(order_status = '8',`cope_money`,0)) as problem_money,
			sum(case when order_status = '7' then 1 else 0 end) as not_pass_num,
			sum(if(order_status = '7',`cope_money`,0)) as not_pass_money,
			sum(case when order_status = '2' then 1 else 0 end) as cancel_num,
			sum(if(order_status = '2',`cope_money`,0)) as cancel_money
		";
		
		//print_r($fields);
		$arrCount = Db::name('order_info')->Distinct(true)->field("dept_id")->where($where)->select();
		$count = count($arrCount);
		$sql = "SELECT ".$fields." FROM `xy_order_info` WHERE ".$where."  GROUP BY `dept_id` ORDER BY dept_id asc LIMIT ".($page-1)*$limit.",".$limit;
		// print_r($sql);exit;
		//$dataList = Db::name('order_info')->fetchSql(true)->field($fields)->where($where)->limit(($page-1)*$limit,$limit)->order("createname asc")->group("createname")->select();
		$dataList = Db::query($sql);
		$department = Cache::get('department');
		$users = Cache::get('users');
		
		foreach($dataList as $key=>&$val){
			$val["dept_name"] = $department[$val["dept_id"]]["department_name"];
			
			//实际销售单数/总订单数
			$val["audit_rate2"] = ($val["actual_sales_num"]/$val["total_order_num"])*100;
			$val["audit_rate"] = round($val["audit_rate2"],2)."%";;  //审核率
			
		}
		
		$rowsList = count($dataList) ? $dataList : false;
        $arrCate["code"] = 0;
        $arrCate["msg"] = '';
        $arrCate["count"] = $count;
        $arrCate["data"] = $rowsList;
        return $arrCate;
		
	}


    public function conversationReportData()
	{
		$page = $_REQUEST["page"];
		$limit = $_REQUEST["limit"];
		$createtime_start = $_REQUEST['createtime_start'];
		$createtime_end = $_REQUEST['createtime_end'];
		$create_user = $_REQUEST['create_user'];
		$dept_id = $_REQUEST['dept_id'];
		$username = $_SESSION["user_info"]["username"];
		$d_id = $_SESSION['user_info']['d_id'];
		$arrDep = $this->getDepTreeData();
		$deptst = $this->getMeAndSubDeptID($arrDep,$dept_id);  //取上级部门
		$deptSet = rtrim($deptst,",");
		
		$deptst2 = $this->getMeAndSubDeptID($arrDep,$d_id);  //取上级部门
		$deptSet2 = rtrim($deptst2,",");
		
		if(!$dept_id){
			$deptId = $deptSet2;
		}else{
			$deptId = $deptSet;
		}

		$where = "workno is not null AND workno != '' ";
		$where .= empty($createtime_start) ? "" : " AND calldate >= '$createtime_start'";
		$where .= empty($createtime_end) ? "" : " AND calldate <= '$createtime_end'";
		$where .= empty($create_user) ? "" : " AND workno = '$create_user'";
		/*if($username != "admin" || $dept_id){
			$where .= " AND dept_id in ($deptId)";
		}*/
		
		$fields = "workno,dept_id,count(*) as call_total,
			sum(case when disposition = 'ANSWERED' then 1 else 0 end) as total_answered_num,
			sum(case when disposition != 'ANSWERED' then 1 else 0 end) as total_noanswer_num,
			sum(case when calltype = 'IN' then 1 else 0 end) as in_call_total,
			sum(case when calltype = 'IN' AND disposition = 'ANSWERED' then 1 else 0 end) as in_answered_num,
			sum(case when calltype = 'IN' AND disposition != 'ANSWERED' then 1 else 0 end) as in_noanswer_num,
			sum(case when calltype = 'OUT' then 1 else 0 end) as out_call_total,
			sum(case when calltype = 'OUT' AND disposition = 'ANSWERED' then 1 else 0 end) as out_answered_num,
			sum(case when calltype = 'OUT' AND disposition != 'ANSWERED' then 1 else 0 end) as out_noanswer_num,
			sum(billsec) as call_billsec_total,
			sum(case when calltype IN ('AUTOCALL','PREVIEW') AND disposition = 'ANSWERED' then 1 else 0 end) as autocall_answered_num,
			sum(if(calltype = 'IN',`billsec`,0)) as in_billsec_total,
			sum(if(calltype = 'IN' AND disposition = 'ANSWERED',`billsec`,0)) as in_answered_billsec_total,
			sum(if(calltype = 'IN' AND disposition != 'ANSWERED',`billsec`,0)) as in_noanswer_billsec_total,
			sum(if(calltype = 'OUT',`billsec`,0)) as out_billsec_total,
			sum(if(calltype = 'OUT' AND disposition = 'ANSWERED',`billsec`,0)) as out_answered_billsec_total,
			sum(if(calltype = 'OUT' AND disposition != 'ANSWERED',`billsec`,0)) as out_noanswer_billsec_total,
			sum(case when calltype = 'OUT' AND disposition != 'ANSWERED' AND billsec <= '$out_call_noanswer' then 1 else 0 end) as out_x_noanswer_num,
			sum(case when calltype = 'OUT' AND disposition = 'ANSWERED' AND billsec >= '$out_call_answered' then 1 else 0 end) as out_x_answered_num,
			sum(if(calltype IN ('AUTOCALL','PREVIEW') AND disposition = 'ANSWERED',`billsec`,0)) as autocall_answered_billsec_total
		";
		
		//print_r($fields);
		$arrCount = Db::table('asteriskcdrdb.cdr')->Distinct(true)->field("workno")->where($where)->select();
		$count = count($arrCount);
		$sql = "SELECT ".$fields." FROM asteriskcdrdb.`cdr` WHERE ".$where."  GROUP BY `workno` ORDER BY workno asc LIMIT ".($page-1)*$limit.",".$limit;
		//$dataList = Db::name('order_info')->fetchSql(true)->field($fields)->where($where)->limit(($page-1)*$limit,$limit)->order("createname asc")->group("createname")->select();
		$dataList = Db::query($sql);

		// print_r($sql);exit;
		// $department = Cache::get('department');
		$users = Cache::get('users');
		
		foreach($dataList as $key=>&$val){
			$val["dept_name"] = $department[$val["dept_id"]]["department_name"];
			$val["cn_name"] = $users[$val["workno"]]["cn_name"];
			
			$val["total_call_rate2"] = ($val["total_answered_num"]/$val["call_total"])*100;
			$val["total_call_rate"] = round($val["total_call_rate2"],2)."%";;  //总接通率
			
			$val["in_call_rate2"] = ($val["in_answered_num"]/$val["in_call_total"])*100;
			$val["in_call_rate"] = round($val["in_call_rate2"],2)."%";;  //呼入接通率
			
			$val["out_call_rate2"] = ($val["out_answered_num"]/$val["out_call_total"])*100;
			$val["out_call_rate"] = round($val["out_call_rate2"],2)."%";;  //外呼接通率
			
			//外呼有效数（X秒以上）/外呼总数
			$val["out_x_answered_rate2"] = ($val["out_x_answered_num"]/$val["out_call_total"])*100;
			$val["out_x_answered_rate"] = round($val["out_x_answered_rate2"],2)."%";;  //外呼有效率（X秒以上）
			
			$val["in_billsec_avg"] = ceil($val["in_billsec_total"]/$val["in_call_total"]);     //呼入通话平均时长
			$val["in_billsec_avg"] = secendHMS($val["in_billsec_avg"]);
			
			$val["out_billsec_avg"] = ceil($val["out_billsec_total"]/$val["out_call_total"]);     //外呼通话平均时长
			$val["out_billsec_avg"] = secendHMS($val["out_billsec_avg"]);
			
			$val["in_answered_billsec_avg"] = ceil($val["in_answered_billsec_total"]/$val["in_answered_num"]);     //呼入接通平均时长
			$val["in_answered_billsec_avg"] = secendHMS($val["in_answered_billsec_avg"]);
			
			$val["in_noanswer_billsec_avg"] = ceil($val["in_noanswer_billsec_total"]/$val["in_noanswer_num"]);     //呼入未接平均时长
			$val["in_noanswer_billsec_avg"] = secendHMS($val["in_noanswer_billsec_avg"]);
			
			$val["out_answered_billsec_avg"] = ceil($val["out_answered_billsec_total"]/$val["out_answered_num"]);     //外呼接通平均时长
			$val["out_answered_billsec_avg"] = secendHMS($val["out_answered_billsec_avg"]);
			
			$val["out_noanswer_billsec_avg"] = ceil($val["out_noanswer_billsec_total"]/$val["out_noanswer_num"]);     //外呼未接平均时长
			$val["out_noanswer_billsec_avg"] = secendHMS($val["out_noanswer_billsec_avg"]);
			
			$val["autocall_answered_billsec_total"] = secendHMS($val["autocall_answered_billsec_total"]);  //自动外呼总时长
			
			
			$val["call_billsec_total"] = secendHMS($val["call_billsec_total"]);  //通话总时长
			$val["in_billsec_total"] = secendHMS($val["in_billsec_total"]);  //呼入通话总时长
			$val["out_billsec_total"] = secendHMS($val["out_billsec_total"]);  //外呼通话总时长
			$val["in_answered_billsec_total"] = secendHMS($val["in_answered_billsec_total"]);  //呼入接通总时长
			$val["in_noanswer_billsec_total"] = secendHMS($val["in_noanswer_billsec_total"]);  //呼入未接总时长
			$val["out_answered_billsec_total"] = secendHMS($val["out_answered_billsec_total"]);  //外呼接通总时长
			$val["out_noanswer_billsec_total"] = secendHMS($val["out_noanswer_billsec_total"]);  //外呼未接总时长
		}
		
		$rowsList = count($dataList) ? $dataList : false;
        $arrCate["code"] = 0;
        $arrCate["msg"] = '';
        $arrCate["count"] = $count;
        $arrCate["data"] = $rowsList;
        return $arrCate;
		
	}



    
	/*
	* 获得一个数组，该数组是一维数组，存放id，名称，id和下一级别部门的id
	*/
	function getDepTreeData(){
		$DepTree = array();//一维数组
		$arr = Db::name('sys_department')->select();
		foreach($arr AS $v){
			$currentId = $v['department_id'];
			$arrSonId = Db::name('sys_department')->field('department_id')->where("department_pid=$currentId")->select();
			$strId = "$currentId";
			foreach($arrSonId AS $row){
				$strId .= "," . $row['department_id'];
			}
			$arrDepTree[$currentId] = Array(
				"id" => $v['department_id'],
				"pid" => $v['department_pid'],
				"name"=> $v['department_name'],
				"meAndSonId"=>$strId,
			);
		}
		//dump($arrDepTree);die;
		return $arrDepTree;
	}

	function getMeAndSubDeptID($arrDep,$dept_id){
		$arrId = explode(',',$arrDep[$dept_id]['meAndSonId']);
		//$str = "'" . $arrDep[$dept_id]['name'] . "',";
		$str = "'" . $arrDep[$dept_id]['id'] . "',";
		if( array_shift($arrId) ){
			foreach( $arrId AS $id ){
				$str .= $this->getMeAndSubDeptID($arrDep,$id);
			}
		}
		return $str;
		
	}
	
	
	









}